package com.zxd.interview.util.excel;

import com.zxd.interview.util.date.DateHelper;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Removal;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.thymeleaf.util.DateUtils;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 该类实现了将一组对象转换为Excel表格，并且可以从Excel表格中读取到一组List对象中 该类利用了BeanUtils框架中的反射完成
 * 使用该类的前提，在相应的实体对象上通过ExcelReources来完成相应的注解
 *
 * @author zxd
 */
@SuppressWarnings({"rawtypes"})
public class ExcelUtil {


    int CELL_TYPE_NUMERIC = 0;
    int CELL_TYPE_STRING = 1;
    int CELL_TYPE_FORMULA = 2;
    int CELL_TYPE_BLANK = 3;
    int CELL_TYPE_BOOLEAN = 4;
    int CELL_TYPE_ERROR = 5;
    private static ExcelUtil eu = new ExcelUtil();

    protected ExcelUtil() {
    }

    public static ExcelUtil getInstance() {
        return eu;
    }

    /**
     * 根据标题获取相应的方法名称
     *
     * @param eh
     * @return
     */
    private String getMethodName(ExcelHeader eh) {
        String mn = eh.getMethodName().substring(3);
        mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
        return mn;
    }

    /**
     * 写数据进入excel
     *
     * @param objs
     * @param clz
     * @param isXssf
     * @return
     */
    private Workbook handleObj2Excel(List objs, Class clz, boolean isXssf) {
        Workbook wb = null;
        try {
            if (isXssf) {
                wb = new XSSFWorkbook();
            } else {
                wb = new HSSFWorkbook();
            }
            Sheet sheet = wb.createSheet();
            sheet.setDefaultColumnWidth(15);

            Row r = sheet.createRow(0);
            List<ExcelHeader> headers = getHeaderList(clz);
            Collections.sort(headers);

            // 写标题
            for (int i = 0; i < headers.size(); i++) {
                r.createCell(i).setCellValue(headers.get(i).getTitle());
            }
            // 写数据
            Object obj = null;
            for (int i = 0; i < objs.size(); i++) {
                r = sheet.createRow(i + 1);
                obj = objs.get(i);
                for (int j = 0; j < headers.size(); j++) {
                    String property = BeanUtils.getProperty(obj, getMethodName(headers.get(j)));
                    if (StringUtils.isNumeric(property) && property.length() < 10) {
                        property = numberZeroHandHandler(property);
                        Long pro = NumberUtils.createLong(property);
                        r.createCell(j).setCellValue(pro);
                    } else {
                        r.createCell(j).setCellValue(property);
                    }
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 写数据进入excel (POI 3.11 SXSSFWorkbook)
     *
     * @param objs
     * @param clz
     * @return
     */
    private SXSSFWorkbook handleObj2ExcelSXSSF(List objs, Class clz) {
        SXSSFWorkbook wb = null;
        try {
            wb = new SXSSFWorkbook();
            Sheet sheet = wb.createSheet();

            Row r = sheet.createRow(0);
            List<ExcelHeader> headers = getHeaderList(clz);
            Collections.sort(headers);

            // 写标题
            for (int i = 0; i < headers.size(); i++) {
                r.createCell(i).setCellValue(headers.get(i).getTitle());
            }
            // 写数据
            Object obj = null;
            for (int i = 0; i < objs.size(); i++) {
                r = sheet.createRow(i + 1);
                obj = objs.get(i);
                for (int j = 0; j < headers.size(); j++) {
                    r.createCell(j).setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 写数据进入excel
     *
     * @param objs
     * @param clz
     * @param isXssf
     * @return
     */
    private Workbook handleObj2ExcelHasMap(List objs, Class clz, boolean isXssf, Map map) {
        Workbook wb = null;
        try {
            if (isXssf) {
                wb = new XSSFWorkbook();
            } else {
                wb = new HSSFWorkbook();
            }
            Sheet sheet = wb.createSheet();
            sheet.setDefaultColumnWidth(20);

            Row r = sheet.createRow(0);
            List<ExcelHeader> headers = getHeaderListHasMap(clz, map);
            Collections.sort(headers);

            // 写标题
            for (int i = 0; i < headers.size(); i++) {
                r.createCell(i).setCellValue(headers.get(i).getTitle());
            }
            // 写数据
            Object obj = null;
            for (int i = 0; i < objs.size(); i++) {
                r = sheet.createRow(i + 1);
                obj = objs.get(i);
                for (int j = 0; j < headers.size(); j++) {
                    String property = BeanUtils.getProperty(obj, getMethodName(headers.get(j)));
                    if (StringUtils.isNumeric(property) && property.length() < 10) {
                        property = numberZeroHandHandler(property);
                        Long pro = NumberUtils.createLong(property);
                        r.createCell(j).setCellValue(pro);
                    } else {
                        r.createCell(j).setCellValue(property);
                    }
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 写数据进入excel (POI 3.11 SXSSFWorkbook)
     *
     * @param objs
     * @param clz
     * @return
     */
    private SXSSFWorkbook handleObj2ExcelHasMapSXSSF(List objs, Class clz, Map map) {
        SXSSFWorkbook wb = null;
        try {
            wb = new SXSSFWorkbook();
            Sheet sheet = wb.createSheet();

            Row r = sheet.createRow(0);
            List<ExcelHeader> headers = getHeaderListHasMap(clz, map);
            Collections.sort(headers);

            // 写标题
            for (int i = 0; i < headers.size(); i++) {
                r.createCell(i).setCellValue(headers.get(i).getTitle());
            }
            // 写数据
            Object obj = null;
            for (int i = 0; i < objs.size(); i++) {
                r = sheet.createRow(i + 1);
                obj = objs.get(i);
                for (int j = 0; j < headers.size(); j++) {
                    r.createCell(j).setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于路径的导出
     *
     * @param outPath 导出路径
     * @param objs    对象列表
     * @param clz     对象类型
     * @param isXssf  是否是2007版本
     */
    public void exportObj2Excel(String outPath, List objs, Class clz, boolean isXssf) {
        Workbook wb = handleObj2Excel(objs, clz, isXssf);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(outPath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }

            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于路径的导出 (POI 3.11 SXSSFWorkbook)
     *
     * @param outPath 导出路径
     * @param objs    对象列表
     * @param clz     对象类型
     */
    public void exportObj2ExcelSXSSF(String outPath, List objs, Class clz) {
        SXSSFWorkbook wb = handleObj2ExcelSXSSF(objs, clz);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(outPath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }

            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于流
     *
     * @param os     输出流
     * @param objs   对象列表
     * @param clz    对象类型
     * @param isXssf 是否是2007版本
     */
    public void exportObj2Excel(OutputStream os, List objs, Class clz, boolean isXssf) {
        try {
            Workbook wb = handleObj2Excel(objs, clz, isXssf);
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 从文件路径读取相应的Excel文件到对象列表
     *
     * @param path     文件路径下的path
     * @param clz      对象类型
     * @param readLine 开始行，注意是标题所在行
     * @param tailLine 底部有多少行，在读入对象时，会减去这些行
     * @param num      标题所在行所占行数
     * @return
     */
    public List<Object> readExcel2ObjsByPath(String path, Class clz, int readLine, int tailLine, int num) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(new File(path));
            return num == 1 ? handlerExcel2Objs(wb, clz, readLine, tailLine)
                    : handlerExcel2Objs(wb, clz, readLine, tailLine, num);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从文件路径读取相应的Excel文件到对象列表,且可以指定修改大于某列的类型为数字类型（适用于读取小数）
     *
     * @param path                文件路径下的path
     * @param clz                 对象类型
     * @param readLine            开始行，注意是标题所在行
     * @param tailLine            底部有多少行，在读入对象时，会减去这些行
     * @param num                 标题所在行所占行数
     * @param ChangeCellTypeToNum 是否启用修改列类型
     * @param cellOrder           大于第几列的需要修改为数字类型（从0开始）
     * @return
     */
    public List<Object> readExcel2ObjsByPath(String path, Class clz, int readLine, int tailLine, int num, boolean ChangeCellTypeToNum, int cellOrder) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(new File(path));
            return num == 1 ? handlerExcel2Objs(wb, clz, readLine, tailLine)
                    : handlerExcel2Objs(wb, clz, readLine, tailLine, num, ChangeCellTypeToNum, cellOrder);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取excel数据
     *
     * @param wb
     * @param clz
     * @param readLine
     * @param tailLine
     * @param num
     * @param isChangeCellType 是否修改列类型为数字
     * @param cellOrder        第几列开始需要修改
     * @return
     */
    private List<Object> handlerExcel2Objs(Workbook wb, Class clz, int readLine, int tailLine, int num, boolean isChangeCellType, int cellOrder) {
        Sheet sheet = wb.getSheetAt(0);
        List<Object> objs = null;
        try {
            Row row = sheet.getRow(readLine);
            objs = new ArrayList<Object>();
            Map<Integer, String> maps = getHeaderMap(row, clz);
            if (maps == null || maps.size() <= 0) {
                throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行");
            }
            for (int i = readLine + num; i <= sheet.getLastRowNum() - tailLine; i++) {
                row = sheet.getRow(i);
                if (row == null) {
                    return objs;
                }
                Object obj = clz.newInstance();
                for (Cell c : row) {
                    int ci = c.getColumnIndex();
                    if (maps.get(ci) != null) {
                        String mn = maps.get(ci).substring(3);
                        mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
                        if (isChangeCellType && ci >= cellOrder) {
                            c.setCellType(CellType.NUMERIC);
                            double value = c.getNumericCellValue();
                            BeanUtils.copyProperty(obj, mn, value);
                        } else {
                            BeanUtils.copyProperty(obj, mn, getCellValue(c));
                        }
                    }
                }
                objs.add(obj);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return objs;
    }


    /**
     * 从文件路径读取相应的Excel文件到对象列表
     *
     * @param path     文件路径下的path
     * @param clz      对象类型
     * @param readLine 开始行，注意是标题所在行
     * @param tailLine 底部有多少行，在读入对象时，会减去这些行
     * @param num      标题所在行所占行数
     * @param isline   是否显示行号
     * @return
     */
    public List<Object> readExcel2ObjsByPath(String path, Class clz, int readLine, int tailLine, int num,
                                             Boolean isLine) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(new File(path));
            return isLine ? handlerExcel2ObjsHasLineNumber(wb, clz, readLine, tailLine, num)
                    : handlerExcel2Objs(wb, clz, readLine, tailLine, num);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从文件路径读取相应的Excel文件到对象列表，并把行号设置属性中
     *
     * @param path       文件路径下的path
     * @param clz        对象类型
     * @param readLine   开始行，注意是标题所在行
     * @param tailLine   底部有多少行，在读入对象时，会减去这些行
     * @param num        标题所在行所占行数
     * @param lineNumber 是否显示行号
     * @return
     */
    private List<Object> handlerExcel2ObjsHasLineNumber(Workbook wb, Class clz, int readLine, int tailLine, int num) {
        Sheet sheet = wb.getSheetAt(0);
        List<Object> objs = null;
        try {
            Row row = sheet.getRow(readLine);
            objs = new ArrayList<Object>();
            Map<Integer, String> maps = getHeaderMap(row, clz);
            if (maps == null || maps.size() <= 0) {
                throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行");
            }

            for (int i = readLine + num; i <= sheet.getLastRowNum() - tailLine; i++) {
                row = sheet.getRow(i);
// 				不用用row.getCell(0)来判断 应该有一些数据的第一行有空值
//				if (row.getCell(0) == null || StringUtils.isBlank(getCellValue(row.getCell(0)))) {
//					return objs;
//				}
                if (row == null) {
                    return objs;
                }
                Object obj = clz.newInstance();
                for (Cell c : row) {
                    int ci = c.getColumnIndex();
                    if (maps.get(ci) != null) {
                        String mn = maps.get(ci).substring(3);
                        mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
                        if (StringUtils.isNotBlank(getCellValue(c))) {
                            BeanUtils.copyProperty(obj, mn, getCellValue(c));
                        }
                    }
                }
                // 添加行号
                BeanUtils.copyProperty(obj, "lineNumber", i + 1);
                objs.add(obj);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return objs;
    }

    /**
     * 从文件路径读取相应的Excel文件到对象列表
     *
     * @param path     文件路径下的path
     * @param clz      对象类型
     * @param readLine 开始行，注意是标题所在行
     * @param tailLine 底部有多少行，在读入对象时，会减去这些行
     * @param num      标题所在行所占行数
     * @return
     */
    private List<Object> handlerExcel2Objs(Workbook wb, Class clz, int readLine, int tailLine, int num) {
        Sheet sheet = wb.getSheetAt(0);
        List<Object> objs = null;
        try {
            Row row = sheet.getRow(readLine);
            objs = new ArrayList<Object>();
            Map<Integer, String> maps = getHeaderMap(row, clz);
            if (maps == null || maps.size() <= 0) {
                throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行");
            }
            for (int i = readLine + num; i <= sheet.getLastRowNum() - tailLine; i++) {
                row = sheet.getRow(i);
                if (row == null) {
                    return objs;
                }
                //	//不用用row.getCell(0)来判断 应该有一些数据的第一行有空值
//				if (row.getCell(0) == null || StringUtils.isBlank(getCellValue(row.getCell(0)))) {
//					return objs;
//				}
                Object obj = clz.newInstance();
                for (Cell c : row) {
                    int ci = c.getColumnIndex();
                    if (maps.get(ci) != null) {
                        String mn = maps.get(ci).substring(3);
                        mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
                        BeanUtils.copyProperty(obj, mn, getCellValue(c));
                    }
                }
                objs.add(obj);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return objs;
    }

    /**
     * 读取表格
     *
     * @param wb
     * @param clz
     * @param readLine
     * @param tailLine
     * @param sheetNumber 第几张表格
     * @return
     */
    protected List<Object> handlerExcel2ObjsSheet(Workbook wb, Class clz, int readLine, int tailLine, int sheetNumber) {
        Sheet sheet = wb.getSheetAt(sheetNumber);
        List<Object> objs = null;
        try {
            Row row = sheet.getRow(readLine);
            objs = new ArrayList<Object>();
            Map<Integer, String> maps = getHeaderMap(row, clz);
            if (maps == null || maps.size() <= 0) {
                throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行");
            }

            for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) {
                row = sheet.getRow(i);

                Object obj = clz.newInstance();

                for (Cell c : row) {

                    int ci = c.getColumnIndex();
                    if (maps.get(ci) != null) {
                        String mn = maps.get(ci).substring(3);
                        mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
                        if (StringUtils.isNotBlank(this.getCellValue(c))) {
                            BeanUtils.copyProperty(obj, mn, this.getCellValue(c));
                        }
                    }
                }
                objs.add(obj);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return objs;
    }

    /**
     * 从类路径读取相应的Excel文件到对象列表
     *
     * @param path     类路径下的path
     * @param clz      对象类型
     * @param readLine 开始行，注意是标题所在行
     * @param tailLine 底部有多少行，在读入对象时，会减去这些行
     * @return
     */
    public List<Object> readExcel2ObjsByClasspath(String path, Class clz, int readLine, int tailLine) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(ExcelUtil.class.getResourceAsStream(path));
            return handlerExcel2Objs(wb, clz, readLine, tailLine);
        }  catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从文件路径读取相应的Excel文件到对象列表
     *
     * @param path     文件路径下的path
     * @param clz      对象类型
     * @param readLine 开始行，注意是标题所在行
     * @param tailLine 底部有多少行，在读入对象时，会减去这些行
     * @return
     */
    public List<Object> readExcel2ObjsByPath(String path, Class clz, int readLine, int tailLine) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(new File(path));
            return handlerExcel2Objs(wb, clz, readLine, tailLine);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Object> readExcel2ObjsByPathSheet(String path, Class clz, int readLine, int tailLine, int sheetNumber) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(new File(path));
            return handlerExcel2ObjsSheet(wb, clz, readLine, tailLine, sheetNumber);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从类路径读取相应的Excel文件到对象列表，标题行为0，没有尾行
     *
     * @param path 路径
     * @param clz  类型
     * @return 对象列表
     */
    public List<Object> readExcel2ObjsByClasspath(String path, Class clz) {
        return this.readExcel2ObjsByClasspath(path, clz, 0, 0);
    }

    /**
     * 从文件路径读取相应的Excel文件到对象列表，标题行为0，没有尾行
     *
     * @param path 路径
     * @param clz  类型
     * @return 对象列表
     */
    public List<Object> readExcel2ObjsByPath(String path, Class clz) {
        return this.readExcel2ObjsByPath(path, clz, 0, 0);
    }

    protected String getCellValue(Cell c) {
        return null;
//
//        String o = null;
//
//        switch (c.getCellType()) {
//            case CellType.BLANK:
//                o = "";
//                break;
//            case CELL_TYPE_BOOLEAN:
//                o = String.valueOf(c.getBooleanCellValue());
//                break;
//            case CELL_TYPE_FORMULA:
//                FormulaEvaluator evaluator = c.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
//                evaluator.evaluateFormulaCell(c);
//                CellValue cellValue = evaluator.evaluate(c);
//                o = cellValue.getStringValue();
//                break;
//            case CELL_TYPE_NUMERIC:
//                // o = String.valueOf(c.getNumericCellValue());
//                if (c.getNumericCellValue() == -1) {
//                    return null;
//                }
//                if (c.getCellStyle().getDataFormat() == 176) {
//                    return DateUtils.format(HSSFDateUtil.getJavaCalendar(c.getNumericCellValue()).getTime(), Locale.forLanguageTag(DateHelper.yyyyMMdd));
//                }
//                if (HSSFDateUtil.isCellDateFormatted(c)) {
//                    SimpleDateFormat sdf = null;
//
//                    if (c.getCellStyle().getDataFormat() == 22) {
//                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                    } else {// 日期
//                        sdf = new SimpleDateFormat(DateHelper.yyyyMMdd);
//                    }
//                    Date date = c.getDateCellValue();
//                    o = sdf.format(date);
//                } else if (c.getCellStyle().getDataFormat() == 58) {
//                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
//                    SimpleDateFormat sdf = new SimpleDateFormat(DateHelper.yyyyMMdd);
//                    double value = c.getNumericCellValue();
//                    Date date = DateUtil.getJavaDate(value);
//                    o = sdf.format(date);
//                } else {
//
//                    double value = c.getNumericCellValue();
//                    CellStyle style = c.getCellStyle();
//                    // 为了正常读取 excel 中的号码 在构建对象时传入参数"0"
//                    DecimalFormat format = new DecimalFormat("0");
//                    String temp = style.getDataFormatString();
//                    // 单元格设置成常规
//                    if (temp.equals("General")) {
//                        format.applyPattern("#");
//                    }
//                    String  values =  String.valueOf(value);
//                    if(isNumber(values) && !values.endsWith(".0")){
//                        // 注释掉原来获取单元格值的方式
//                         o = String.valueOf(value);
//                    } else{
//                        // 选用下面这个来获取单元格的值
//                        o = format.format(value);
//                    }
//                }
//                break;
//            case CELL_TYPE_STRING:
//                o = c.getStringCellValue();
//                break;
//            default:
//                o = null;
//                break;
//        }
//        return o;
    }

    /**
     *判断是否是小数点
     * @param str
     * @return
     */
    private boolean isNumber(String str) {
        if (StringUtils.isBlank(str)) {
            return false;
        }
        String reg = "\\d+\\.\\d+$";
        return str.matches(reg);

    }
    protected List<Object> handlerExcel2Objs(Workbook wb, Class clz, int readLine, int tailLine) {
        Sheet sheet = wb.getSheetAt(0);
        List<Object> objs = null;
        try {
            Row row = sheet.getRow(readLine);
            objs = new ArrayList<Object>();
            Map<Integer, String> maps = getHeaderMap(row, clz);
            if (maps == null || maps.size() <= 0) {
                throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行");
            }
            for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) {
                row = sheet.getRow(i);

                if (row == null) {
                    return objs;
                }
                //不用用row.getCell(0)来判断 应该有一些数据的第一行有空值
//				if (row.getCell(0) == null || StringUtils.isBlank(getCellValue(row.getCell(0)))) {
//					return objs;
//				}
                Object obj = clz.newInstance();

                for (Cell c : row) {

                    int ci = c.getColumnIndex();
                    if (maps.get(ci) != null) {
                        String mn = maps.get(ci).substring(3);
                        mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
                        if (StringUtils.isNotBlank(this.getCellValue(c))) {
                            BeanUtils.copyProperty(obj, mn, this.getCellValue(c));
                        }
                    }
                }
                objs.add(obj);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return objs;
    }

    public List<ExcelHeader> getHeaderList(Class clz) {
        List<ExcelHeader> headers = new ArrayList<ExcelHeader>();
        Method[] ms = clz.getMethods();
        for (Method m : ms) {
            String mn = m.getName();
            if (mn.startsWith("get")) {
                if (m.isAnnotationPresent(ExcelResources.class)) {
                    ExcelResources er = m.getAnnotation(ExcelResources.class);
                    headers.add(new ExcelHeader(er.title(), er.order(), er.column(), mn));
                }
            }
        }
        return headers;
    }

    /**
     * 获取表头，只获取在map内的
     *
     * @param clz
     * @return
     */
    public List<ExcelHeader> getHeaderListHasMap(Class clz, Map map) {
        List<ExcelHeader> headers = new ArrayList<ExcelHeader>();
        Method[] ms = clz.getDeclaredMethods();
        for (Method m : ms) {
            String mn = m.getName();
            if (map.get(mn) != null && mn.startsWith("get")) {
                if (m.isAnnotationPresent(ExcelResources.class)) {
                    ExcelResources er = m.getAnnotation(ExcelResources.class);
                    headers.add(new ExcelHeader(er.title(), er.order(), er.column(), mn));
                }
            }
        }
        return headers;
    }

    protected Map<Integer, String> getHeaderMap(Row titleRow, Class clz) {
        List<ExcelHeader> headers = getHeaderList(clz);
        Map<Integer, String> maps = new HashMap<Integer, String>(32);
        // 列号

        for (Cell c : titleRow) {

            String title = c.getStringCellValue();

            for (ExcelHeader eh : headers) {
                // 判断列号是否为空，不为空通过列名设置表头
                if (StringUtils.isNotBlank(eh.getColumn())) {
                    int ehIndex = Integer.valueOf(eh.getColumn());
                    int currentCellIndex = c.getColumnIndex();
                    if (ehIndex == currentCellIndex) {
                        maps.put(c.getColumnIndex(), eh.getMethodName().replace("get", "set"));
                        break;
                    }

                }
                if (eh.getTitle().equals(title.trim())) {
                    maps.put(c.getColumnIndex(), eh.getMethodName().replace("get", "set"));
                    break;
                }
            }
        }
        return maps;
    }

    public void exportObj2Excel(OutputStream os, List objs, Class clz, boolean isXssf,
                                Map map) {

        try {
            Workbook wb = handleObj2ExcelHasMap(objs, clz, isXssf, map);
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于路径的导出
     *
     * @param outPath 导出路径
     * @param objs    对象列表
     * @param clz     对象类型
     * @param isXssf  是否是2007版本
     */
    public void exportObj2Excel(String outPath, List objs, Class clz, boolean isXssf, Map map) {
        Workbook wb = handleObj2ExcelHasMap(objs, clz, isXssf, map);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(outPath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于路径的导出 (POI 3.11 SXSSFWorkbook)
     *
     * @param outPath 导出路径
     * @param objs    对象列表
     * @param clz     对象类型
     */
    public void exportObj2ExcelSXSSF(String outPath, List objs, Class clz, Map map) {
        SXSSFWorkbook wb = handleObj2ExcelHasMapSXSSF(objs, clz, map);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(outPath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 去掉0开头的数字前面的0
     *
     * @param parm 需要处理的数字
     * @return
     */
    private static String numberZeroHandHandler(String parm) {
        String result = parm.replaceAll("^(0+)", "");
        return StringUtils.isBlank(result) ? "0" : result;
    }
}
